﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Threading;
using DevExpress.XtraBars;
using DevExpress.XtraEditors;
using ZCrawler;
using Microsoft.SqlServer.Management.Smo;
using NCrawler.Extensions;

namespace ZCrawlerManager
{
    public partial class FrmPublish : DevExpress.XtraEditors.XtraForm
    {
        public DialogResult result { get; set; }
        public ZPublisher publisher { get; set; }
        public ZPublisherMapper mapper { get; set; }
        private DatabaseCollection dbCollection { get; set; }
        private List<StoredProcedure> spCollection { get; set; }
        private string[] fields { get; set; }
        private Server srv;
        public FrmPublish()
        {
            InitializeComponent();
            chkEnable.Checked = true;
            chkEnable.Checked = false;
        }
        public FrmPublish(ZPublisher thePublisher,string[] fields_)
        {
            InitializeComponent();
            chkEnable.Checked = true;
            chkEnable.Checked = false;
            cmbNaming.Properties.Items.AddRange(Enum.GetNames(typeof(ZDocumentNamingMethod)));

            //Init
            if (fields_ == null) return;
            fields = fields_;
            foreach (string field in fields)
            {
                BarButtonItem item = new BarButtonItem(barManager1, field);
                item.ItemClick += item_ItemClick;
                drpMenu.AddItem(item);
            }
            BarButtonItem time_item = new BarButtonItem(barManager1, "time");
            time_item.ItemClick += time_item_ItemClick;
            drpMenu.AddItem(time_item);
            

            if (thePublisher == null) return;
            publisher = thePublisher;
            mapper = publisher.mapper;
            this.chkEnable.Checked = publisher.isActivated;
            this.chkSQL.Checked = publisher.media == PublisherMedia.SQL;
            this.chkEXCEL.Checked = !chkSQL.Checked;
            if(publisher.media == PublisherMedia.SQL)
            {
                txtServer.Text = ((ZSQLPublisher)publisher).server;
                cmbDB.EditValue = ((ZSQLPublisher)publisher).database;
                cmbSP.EditValue = ((ZSQLPublisher)publisher).sql_command_type == CommandType.StoredProcedure ?
                    ((ZSQLPublisher)publisher).sql_command_text : null;
                txtSQLCommand.Text = ((ZSQLPublisher)publisher).sql_command_type == CommandType.Text ? ((ZSQLPublisher)publisher).sql_command_text : "";
            }
            else if(publisher.media == PublisherMedia.EXCEL)
            {
                btnStorePath.EditValue = ((ZDocumentPublisher)publisher).store_path;
                cmbNaming.EditValue = ((ZDocumentPublisher)publisher).naming_method.ToString();
            }
        }

        void time_item_ItemClick(object sender, ItemClickEventArgs e)
        {
            string date_time = DateTime.UtcNow.ToString();
            int startIndex = txtSQLCommand.SelectionStart;
            string command = txtSQLCommand.Text.Insert(txtSQLCommand.SelectionStart, date_time);
            txtSQLCommand.Text = command;
            txtSQLCommand.SelectionStart = startIndex + date_time.Length;
        }

        void item_ItemClick(object sender, ItemClickEventArgs e)
        {
            string selected = "@" + e.Item.Caption;
            int startIndex = txtSQLCommand.SelectionStart;
            string command = txtSQLCommand.Text.Insert(txtSQLCommand.SelectionStart, selected);
            txtSQLCommand.Text = command;
            txtSQLCommand.SelectionStart = startIndex + selected.Length;
        }
        private void chkEnable_CheckedChanged(object sender, EventArgs e)
        {
            lcGroupExcel.Enabled = chkEnable.Checked;
            lcGroupMedia.Enabled = chkEnable.Checked;
            lcGroupSQL.Enabled = chkEnable.Checked;
            lcGroupExcel.Enabled = chkEXCEL.Checked && lcGroupMedia.Enabled;
            lcGroupSQL.Enabled = chkSQL.Checked && lcGroupMedia.Enabled;
        }

        private void chkSQL_CheckedChanged(object sender, EventArgs e)
        {
            chkEXCEL.Checked = !chkSQL.Checked;
            lcGroupSQL.Enabled = chkSQL.Checked && chkEnable.Checked;
        }

        private void chkEXCEL_CheckedChanged(object sender, EventArgs e)
        {
            chkSQL.Checked = !chkEXCEL.Checked;
            lcGroupExcel.Enabled = chkEXCEL.Checked && chkEnable.Checked;
        }

        private void btnOK_Click(object sender, EventArgs e)
        {
            if(!chkEnable.Checked)
            {
                publisher = null;
            }
            else
            {
                try
                {
                    if (mapper == null) { XtraMessageBox.Show("Null mapper"); return; }
                    if (chkSQL.Checked)
                    {
                        publisher = new ZSQLPublisher()
                        {
                            isActivated = true,
                            server = txtServer.Text,
                            database = cmbDB.EditValue.ToString(),
                            table_name = cmbTable.EditValue.ToString(),
                            connection_login_id = txtLoginID.Text.ToString(),
                            connection_login_pass = txtLoginPass.Text.ToString(),
                            sql_command_type = chkSQLCommandType.Checked ? CommandType.StoredProcedure : CommandType.Text,
                            sql_command_text = chkSQLCommandType.Checked ? cmbSP.EditValue.ToString() : txtSQLCommand.Text,
                            mapper = this.mapper
                        };
                    }
                    else if (chkEXCEL.Checked)
                    {
                        publisher = new ZDocumentPublisher()
                        {
                            isActivated = true,
                            documentType = ZDocuumentType.EXCEL,
                            naming_method = (ZDocumentNamingMethod)Enum.Parse(typeof(ZDocumentNamingMethod),cmbNaming.SelectedItem.ToString()),
                            store_path = btnStorePath.EditValue.ToString(),
                            mapper = this.mapper
                        };
                    }
                }
                catch(Exception ex)
                { XtraMessageBox.Show(ex.Message); return; }
            }
            result = DialogResult.OK;
            this.Close();
        }

        private void txtServer_Properties_Leave(object sender, EventArgs e)
        {
            string sqlServer = txtServer.Text;
            if (sqlServer.IsNullOrEmpty()) return;
            srv = new Server(sqlServer);
            cmbDB.Properties.Items.Clear();
            try
            {
                dbCollection = srv.Databases;
                foreach (Database db in srv.Databases)
                {
                    cmbDB.Properties.Items.Add(db.Name);
                }
            }
            catch(Exception ex)
            {
                dbCollection = null;
            }
        }

        private void cmbDB_Properties_Leave(object sender, EventArgs e)
        {
            int selectedIndex = cmbDB.SelectedIndex;
            cmbTable.Properties.Items.Clear();
            cmbSP.Properties.Items.Clear();
            if (selectedIndex != -1)
            {
                Database selectedDB = dbCollection[selectedIndex];
                foreach (Table tb in selectedDB.Tables)
                {
                    cmbTable.Properties.Items.Add(tb.Name);
                }
                if (!chkSQLCommandType.Checked) return;
                cmbSP.EditValue = "Loading...";
                cmbSP.Enabled = false;
                chkSQLCommandType.Enabled = false;
                Task task = new Task(() =>
                {
                    try
                    {
                        StoredProcedure[] procedures = new StoredProcedure[selectedDB.StoredProcedures.Count];
                        selectedDB.StoredProcedures.CopyTo(procedures, 0);
                        spCollection = procedures.Where(p => p.Owner != "sys").ToList();
                    }
                    catch (Exception) { spCollection = null; }
                });
                task.Start();
                task.ContinueWith((t) =>
                {
                    cmbSP.Invoke(new Action(() =>
                    {
                        if (spCollection != null)
                        {
                            cmbSP.Properties.Items.Clear();
                            cmbSP.Properties.Items.AddRange(spCollection.Select(s => s.Name).ToArray());
                        }
                        cmbSP.Enabled = chkSQLCommandType.Enabled = chkSQLCommandType.Checked;
                        cmbSP.EditValue = null;
                    }));
                    t.Dispose();
                });
            }
        }

        private void btnAutoGenerate_Click(object sender, EventArgs e)
        {
            if(mapper == null)
            {
                XtraMessageBox.Show("Set mapper first");
                return;
            }
            try
            {
                if (cmbTable.SelectedIndex == -1) return;
                Table tb = dbCollection[cmbDB.SelectedIndex].Tables[cmbTable.SelectedIndex];
                string sql = "insert into {0}(".FormatWith(tb.Name);
                foreach (Column c in tb.Columns)
                {
                    sql += "{0},".FormatWith(c.Name);
                }
                sql = sql.TrimEnd(',') + ") values()";
                txtSQLCommand.EditValue = sql;
            }
            catch(Exception ex)
            {
                XtraMessageBox.Show(ex.Message);
            }
        }

        private void chkSQLCommandType_CheckedChanged(object sender, EventArgs e)
        {
            cmbSP.Enabled = chkSQLCommandType.Checked;
            txtSQLCommand.Enabled = btnAutoGenerate.Enabled = !chkSQLCommandType.Checked;
        }

        private void btnStorePath_ButtonClick(object sender, DevExpress.XtraEditors.Controls.ButtonPressedEventArgs e)
        {
            using(FolderBrowserDialog dialog = new FolderBrowserDialog())
            {
                if(dialog.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    btnStorePath.EditValue = dialog.SelectedPath;
                }
            }
        }

        private void btnMapperSeting_Click(object sender, EventArgs e)
        {
            try
            {
                string[] ori_keys = null;
                if (chkSQLCommandType.Checked)
                {
                    if (cmbDB.SelectedIndex == -1 || fields == null)
                    {
                        XtraMessageBox.Show("Null SP or null fields");
                        return;
                    }
                    ori_keys = spCollection[cmbSP.SelectedIndex].Parameters.OfType<StoredProcedureParameter>().Select(p => p.Name).ToArray();
                }
                else
                {
                    Database db = dbCollection[cmbDB.SelectedIndex];
                    ori_keys = db.Tables[cmbTable.SelectedIndex].
                                        Columns.OfType<Column>().
                                        Select(c => c.Name).
                                        Where(c => !c.ToLower().Equals("id")).
                                        ToArray();

                }
                using (FrmMapper frm = new FrmMapper(ori_keys, fields))
                {
                    frm.ShowDialog();
                    if (frm.result == System.Windows.Forms.DialogResult.OK)
                    {
                        ZPublisherMapper mapper_ = frm.mapper;
                        mapper = mapper_;
                    }
                }
            }
            catch(Exception ex)
            { XtraMessageBox.Show(ex.Message); }
        }

        private void cmbDB_SelectedValueChanged(object sender, EventArgs e)
        {
            cmbTable.EditValue = null;
            cmbSP.EditValue = null;
            cmbTable.Properties.Items.Clear();
            cmbSP.Properties.Items.Clear();
        }

        private void cmbSP_EditValueChanged(object sender, EventArgs e)
        {
        }
    }
}